﻿Option Explicit On
'*******************************************************************
' Program: pkgVisio
' Author: Albert E Edlund
' Date:
'
' Purpose:
' Comments/Base: 
'
'*******************************************************************

#Region " Imports "

Imports ADODB
Imports System
Imports System.IO
Imports System.IO.Packaging
Imports System.Windows.Forms


Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

' imports sqlserver types to support sqlserver 2008 r2 types used in dc_rack solution
' c:\program files\microsoft sql server\100\sdk\assemblies\microsoft.sqlserver.types.dll
Imports Microsoft.SqlServer.Types

Imports System.Reflection
Imports System.Runtime.InteropServices

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging

Imports Microsoft.Office.Interop.Visio
Imports Microsoft.Office.Interop.Visio.VisSectionIndices

Imports pkgVisio.clsStructures
Imports pkgVisio.clsDocument


Imports DocumentFormat.OpenXml.Packaging

Imports System.Text
Imports System.Xml
Imports System.Linq
Imports System.Linq.Enumerable
Imports System.Xml.Schema


' visio v15 does not use a xName prefix which may put it into conflict with other open office apps
Imports <xmlns="http://schemas.microsoft.com/office/visio/2012/main">
Imports <xmlns:rs="urn:schemas-microsoft-com:rowset">

' generic namespace for office open xml, used in all of them
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

#End Region



Module modTest


#Region " Properties and Declarations"

    ' 
    '
    '
    Private strVisioXMLDataRecordset = "<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'" + Chr(10) _
    & "xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'" + Chr(10) _
    & "xmlns:rs='urn:schemas-microsoft-com:rowset'" + Chr(10) _
    & "xmlns:z='#RowsetSchema'>" + Chr(10) _
    & "<s:Schema id='RowsetSchema'>" + Chr(10) _
    & "<s:ElementType name='row' content='eltOnly' rs:updatable='true'>" + Chr(10) _
    & "<s:AttributeType name='c1' rs:name='Cities'" + Chr(10) _
    & "rs:number='2' rs:nullable='true' rs:maydefer='true' rs:write='true'>" + Chr(10) _
    & "<s:datatype dt:type='string' dt:maxLength='255' rs:precision='0'/>" + Chr(10) _
    & "</s:AttributeType>" + Chr(10) _
    & "<s:extends type='rs:rowbase'/>" + Chr(10) _
    & "</s:ElementType>" + Chr(10) _
    & "</s:Schema>" + Chr(10) _
    & "<rs:data>" + Chr(10) _
    & "<z:row c1='New York'/>" + Chr(10) _
    & "<z:row c1='London'/>" + Chr(10) _
    & "</rs:data>" + Chr(10) _
    & "</xml>"

    Const strUTF As String = "<?xml version=""1.0"" encoding=""utf-8"" ?> "

    Private _dictMasterCellSections As Dictionary(Of stcCellSection, String)
    Public Property dictMasterCellSections As Dictionary(Of stcCellSection, String)
        Get
            Return _dictMasterCellSections
        End Get
        Set(value As Dictionary(Of stcCellSection, String))
            _dictMasterCellSections = value
        End Set
    End Property

    Private _dictShapeCellSections As Dictionary(Of stcCellSection, String)
    Public Property dictShapeCellSections As Dictionary(Of stcCellSection, String)
        Get
            Return _dictShapeCellSections
        End Get
        Set(value As Dictionary(Of stcCellSection, String))
            _dictShapeCellSections = value
        End Set
    End Property


    Private _maxShapeId As Int32
    Public Property maxShapeId As Int32
        Get
            maxShapeId = _maxShapeId
        End Get
        Set(value As Int32)
            _maxShapeId = value
        End Set
    End Property




#End Region


#Region " -- testing -- extract cell names "


    '
    ' with the loaded v15 drawing read the masters to gain access to the cell names
    ' since they provide the templates for all of the shapes
    '
    Public Sub buildMasterCellNames()

        ' get a copy of the current clsVisPackage
        Dim lclPackage As clsVisPackage = New clsVisPackage
        lclPackage = frmMain.visPackage

        ' make sure the inventory of package parts is complete
        If lclPackage.colPkgPartInfo.Count < 1 Then
            lclPackage.loadPkgAndParts()
        End If

        ' create a local clsMasters and initialize it
        Dim lclMasters As clsMasters = New clsMasters
        Dim lclMaster As clsMaster = New clsMaster

        Try

            ' init our temp dictionary
            _dictMasterCellSections = Nothing
            _dictMasterCellSections = New Dictionary(Of stcCellSection, String)

            ' go find the masters.xml file and load it
            lclMasters.loadMastersXDoc(lclPackage)
            ' go find the individual shape files and load (mirror to a dictionary)
            lclMasters.loadMasters(Nothing)

            Dim intMasters As Integer = lclMasters.dictMastersID.Count
            If intMasters < 1 Then Exit Sub

            lclMaster.visPackage = lclPackage
            'get the master shape data and load it 
            Dim lclMasterShape As stcMasterData = New stcMasterData
            Dim intKey As Integer = -1


            For Each mstrShape In lclMasters.dictMastersID

                ' get the master information
                lclMasterShape = mstrShape.Value
                ' load the shape xdoc information
                lclMaster.loadMasterShapeXDoc(lclMasterShape.mstFile)
                ' pass the master shape data and the dictionary to find out
                ' what cells have been defined
                lclPackage.loadDictCellSection("master", lclMaster.xDoc, dictMasterCellSections)

            Next mstrShape

            'Dim intCellSection As Integer = dictMasterCellSections.Count
            'Dim stcKey As stcCellSection = Nothing
            'For Each cell In dictMasterCellSections
            '    stcKey = cell.Key
            '    With stcKey
            '        Debug.Print(stcKey.csCellName & " " & stcKey.csCellParent)
            '    End With
            'Next cell

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        lclPackage = Nothing
        lclMasters = Nothing
        lclMaster = Nothing

        GC.Collect()


    End Sub

    '
    ' read the current document page-shapes to get the cell names
    '
    Public Sub buildShapeCellNames()

        ' get a copy of the current clsVisPackage
        Dim lclPackage As clsVisPackage = New clsVisPackage
        lclPackage = frmMain.visPackage

        ' make sure the inventory of package parts is complete
        If lclPackage.colPkgPartInfo.Count < 1 Then
            lclPackage.loadPkgAndParts()
        End If

        ' create a local clsPages and initialize it
        Dim lclPages As clsPages = New clsPages
        Dim lclPage As clsPage = New clsPage

        Try

            ' init our temp dictionary
            _dictShapeCellSections = Nothing
            _dictShapeCellSections = New Dictionary(Of stcCellSection, String)

            ' make sure the page and shape counts have been accomplished
            If lclPackage.dictPageShape.Count = 0 Then
                lclPackage.buildPageAndShapeDictionaries()
            End If

            ' go find the pages.xml file and load it
            lclPages.loadPagesXDoc(lclPackage)
            ' also builds the colPages
            lclPages.loadPages(Nothing)

            Dim newPage As stcPageData
            For Each page In lclPages.dictPagesNameU
                newPage = page.Value
                ' load the page xdoc information
                lclPage.loadPageXDoc(lclPackage, page.Value.pgFile)
                lclPackage.loadDictCellSection("shape", lclPage.xDoc, dictShapeCellSections)
            Next page


            'Dim intCellSection As Integer = dictShapeCellSections.Count
            'Dim stcKey As stcCellSection = Nothing
            'For Each cell In dictShapeCellSections
            '    stcKey = cell.Key
            '    With stcKey
            '        Debug.Print(stcKey.csCellName & " " & stcKey.csCellParent)
            '    End With
            'Next cell


        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        lclPackage = Nothing
        lclPages = Nothing
        lclPage = Nothing
        GC.Collect()

    End Sub

    '
    '
    '
    Public Sub getCellNamesFromVisio()

        Try

            Dim visDoc As Microsoft.Office.Interop.Visio.Document = Nothing
            Dim visMaster As Microsoft.Office.Interop.Visio.Master = Nothing
            Dim visPages As Microsoft.Office.Interop.Visio.Pages = Nothing
            Dim visPage As Microsoft.Office.Interop.Visio.Page = Nothing
            Dim visShape As Microsoft.Office.Interop.Visio.Shape = Nothing
            Dim visCell As Microsoft.Office.Interop.Visio.Cell = Nothing
            Dim visSection As Microsoft.Office.Interop.Visio.Section = Nothing

            Dim stcNewCell As stcCellSection = New stcCellSection
            Dim intX As Integer

            visDoc = frmMain.AxDrawingControl1.Document
            For Each visMaster In visDoc.Masters
                Try
                    visShape = visMaster.Shapes.Item(1)
                Catch
                End Try
                If visShape IsNot Nothing Then
                    For intX = 0 To dictMasterCellSections.Count - 1
                        stcNewCell = dictMasterCellSections.ElementAt(intX).Key
                        Dim strCell As String = stcNewCell.csCellName
                        Dim sectionName As String = ""
                        If visShape.CellExists(strCell, False) = True Then
                            visCell = visShape.Cells(strCell)
                            Dim shtSection As Short = visCell.Section
                            sectionName = getVisioSectionName(visCell.Section)
                            ' Debug.Print(visCell.Name & " " & shtSection.ToString & " " & sectionName)
                        End If ' test for cell exists
                    Next intX
                End If ' test for valid shape
            Next visMaster

        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        End Try


    End Sub

    '
    ' go to the visio libraries to get the shapesheet section names
    '
    Private Function getVisioSectionName(ByVal intEnum As Microsoft.Office.Interop.Visio.VisSectionIndices) As String
        Dim strReturn As String = ""

        Dim idxSection As Microsoft.Office.Interop.Visio.VisSectionIndices = intEnum
        strReturn = idxSection.ToString

        Return strReturn

    End Function


#End Region


#Region " -- testing -- export of array to excel "

    '
    '
    '
    Public Sub testWriteArrayToExcel()

        ' a 2 dimensional array of strings
        Dim myArr As Array = Array.CreateInstance(GetType(String), 5, 2)
        myArr.SetValue("cell1", 0, 0)
        myArr.SetValue("parent1", 0, 1)
        myArr.SetValue("cell2", 1, 0)
        myArr.SetValue("parent1", 1, 1)
        myArr.SetValue("cell3", 2, 0)
        myArr.SetValue("parent1", 2, 1)
        myArr.SetValue("cell4", 3, 0)
        myArr.SetValue("parent1", 3, 1)
        myArr.SetValue("cell5", 4, 0)
        myArr.SetValue("parent2", 4, 1)

        'PrintArrayProperties(myArr)

        ' the file name to store the data into
        Dim strPathFileName As String = "c:\temp\" & "Book1.xlsx"

        ' an optional dictionary for where we want to put the header information
        Dim myDict As Dictionary(Of String, String) = New Dictionary(Of String, String)
        myDict.Add("A1", "CellName")
        myDict.Add("B1", "ssSection")

        modExcel.writeExcelWithArray(myArr, strPathFileName, myDict, "A3")

    End Sub

    '
    '
    '
    Public Sub PrintArrayProperties(myArr As Array)

        ' Displays the properties of the Array.
        Console.WriteLine("The Array has {0} dimension(s) and a " _
           + "total of {1} elements.", myArr.Rank, myArr.Length)
        Console.WriteLine(ControlChars.Tab + "Length" + ControlChars.Tab _
           + "Lower" + ControlChars.Tab + "Upper")
        For i = 0 To myArr.Rank - 1
            Console.Write("{0}:" + ControlChars.Tab + "{1}", i, _
               myArr.GetLength(i))
            Console.WriteLine(ControlChars.Tab + "{0}" + ControlChars.Tab _
               + "{1}", myArr.GetLowerBound(i), myArr.GetUpperBound(i))
        Next i

        ' Displays the contents of the Array.

        PrintArrayValues(myArr)

    End Sub

    '
    '
    '
    Public Sub PrintArrayValues(myArr As Array)

        Console.WriteLine("The Array contains the following values:")

        Dim myEnumerator As System.Collections.IEnumerator = _
           myArr.GetEnumerator()
        Dim i As Integer = 0
        Dim cols As Integer = myArr.GetLength(myArr.Rank - 1)
        While myEnumerator.MoveNext()
            If i < cols Then
                i += 1
            Else
                Console.WriteLine("")
                i = 1
            End If
            Console.Write(ControlChars.Tab + "{0}", myEnumerator.Current)
        End While
        Console.WriteLine("")
    End Sub

    '
    '
    '
    Private Sub PrintTextWriterOutput( _
       ByVal writer As System.IO.TextWriter, ByVal caption As String)

        Console.WriteLine("==============================")
        Console.WriteLine(caption)
        Console.WriteLine("==============================")
        Console.WriteLine(writer.ToString())
    End Sub


#End Region


#Region " -- testing -- datatable to xml "

    '
    '
    '
    Public Sub testDataTableToXML()

        Dim ds As New DataSet
        ' build a customer table
        Dim customerTable As DataTable = GetCustomers()
        ' build an order table
        Dim orderTable As DataTable = GetOrders()

        ds.Tables.Add(customerTable)
        ds.Tables.Add(orderTable)

        ds.Relations.Add("CustomerOrder", _
         New System.Data.DataColumn() {customerTable.Columns(0)}, _
         New System.Data.DataColumn() {orderTable.Columns(1)}, True)

        Dim writer As New System.IO.StringWriter
        ' to save the data for the table and all it's descendants
        Dim blnWriteHierarchy As Boolean = False

        customerTable.WriteXml(writer, XmlWriteMode.WriteSchema, blnWriteHierarchy)
        PrintTextWriterOutput(writer, "Customer table, without hierarchy")

        writer = New System.IO.StringWriter
        blnWriteHierarchy = True
        customerTable.WriteXml(writer, XmlWriteMode.WriteSchema, blnWriteHierarchy)
        PrintTextWriterOutput(writer, "Customer table, with hierarchy")

        MsgBox(" write complete ")

    End Sub

    '
    ' build orders datatable
    '
    Private Function GetOrders() As DataTable
        ' Create sample Customers table, in order
        ' to demonstrate the behavior of the DataTableReader.
        Dim table As New DataTable

        ' Create three columns, OrderID, CustomerID, and OrderDate.
        table.Columns.Add(New System.Data.DataColumn("OrderID", GetType(System.Int32)))
        table.Columns.Add(New System.Data.DataColumn("CustomerID", GetType(System.Int32)))
        table.Columns.Add(New System.Data.DataColumn("OrderDate", GetType(System.DateTime)))

        ' Set the OrderID column as the primary key column.
        table.PrimaryKey = New System.Data.DataColumn() {table.Columns(0)}

        table.Rows.Add(New Object() {1, 1, #12/2/2003#})
        table.Rows.Add(New Object() {2, 1, #1/3/2004#})
        table.Rows.Add(New Object() {3, 2, #11/13/2004#})
        table.Rows.Add(New Object() {4, 3, #5/16/2004#})
        table.Rows.Add(New Object() {5, 3, #5/22/2004#})
        table.Rows.Add(New Object() {6, 4, #6/15/2004#})

        table.AcceptChanges()

        Return table
    End Function

    '
    ' build customers datatable
    '
    Private Function GetCustomers() As DataTable
        ' Create sample Customers table, in order
        ' to demonstrate the behavior of the DataTableReader.
        Dim table As New DataTable

        ' Create two columns, ID and Name.
        Dim idColumn As System.Data.DataColumn = table.Columns.Add("ID", _
            GetType(System.Int32))
        table.Columns.Add("Name", GetType(System.String))

        ' Set the ID column as the primary key column.
        table.PrimaryKey = New System.Data.DataColumn() {idColumn}

        table.Rows.Add(New Object() {1, "Mary"})
        table.Rows.Add(New Object() {2, "Andy"})
        table.Rows.Add(New Object() {3, "Peter"})
        table.Rows.Add(New Object() {4, "Russ"})

        table.AcceptChanges()

        Return table
    End Function


#End Region


#Region " add shapes to drawing and then connect "


    '
    ' this is the draw method for adding shapes based on input tables
    '
    Public Sub addTestShapesToPage _
        (ByVal strPackagePath As String, _
         ByVal strPageNameU As String, _
         ByVal blnUseMasters As Boolean, _
         ByVal blnAddNetworkProperties As Boolean)


        Dim strMasterNameU As String = ""
        Dim strNewShapeNameU As String = "NewShape"
        Dim strNewShapeId As String = "1"


        Dim lclVisPackage As clsVisPackage = New clsVisPackage
        lclVisPackage.fileCurrent = strPackagePath


        Dim visMasters As clsMasters = New clsMasters
        Dim visMaster As clsMaster = New clsMaster
        Dim visPages As clsPages = New clsPages
        Dim visPage As clsPage = New clsPage
        Dim visShape As clsShape = New clsShape

        Try

            lclVisPackage.loadPkgAndParts()
            Dim intPartCount As Integer = lclVisPackage.colPkgPartInfo.Count

            visPages.loadPagesXDoc(lclVisPackage)
            ' just build the page dictionary
            visPages.loadPages(Nothing)

            Dim pageData As stcPageData = Nothing
            If visPages.dictPagesNameU.TryGetValue(strPageNameU, pageData) Then
                visPage.loadPageXDoc(lclVisPackage, visPages.dictPagesNameU(strPageNameU).pgFile)
            Else
                MsgBox("Unable to find page " & strPageNameU)
                Exit Sub
            End If

            ' our test drawing should only have three masters
            visMasters.loadMastersXDoc(lclVisPackage)
            ' build the masters dictionary also 
            visMasters.loadMasters(Nothing)
            Dim masterData As stcMasterData = Nothing


            Using package As Package = package.Open(strPackagePath, FileMode.Open, FileAccess.ReadWrite)

                Dim partUriPage As Uri = PackUriHelper.CreatePartUri(New Uri(pageData.pgFile, UriKind.Relative))
                Dim packagePartPage As PackagePart = package.GetPart(partUriPage)

                Dim blnPageHasMasters As Boolean = False
                Dim colMasters As Collection = New Collection
                If lclVisPackage.colPkgPartInfo.Count > 0 Then
                    For Each node As pkgpartInfo In lclVisPackage.colPkgPartInfo
                        If node.pkgpartContentType = clsVisPackage.contXmlMaster _
                            And node.pkgpartParent = pageData.pgFile Then
                            blnPageHasMasters = True
                            colMasters.Add(node.pkgpartPath)
                        End If
                    Next node
                End If

                Dim tempDoc As XDocument = XDocument.Load(XmlReader.Create(packagePartPage.GetStream()))
                ' get the page contents
                If visPage IsNot Nothing Then
                    visPage = Nothing
                End If
                visPage = New clsPage(lclVisPackage, tempDoc)
                visPage.strPgName = strPageNameU
                visPage.strPgNameU = strPageNameU
                ' start the element drilldown on the page
                Dim xelemPageShapes As XElement = Nothing
                Dim blnPageShapes As Boolean = False
                Dim qryContents = visPage.xDoc...<PageContents>
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In qryContents.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    Dim strName As String = LCase(elem.Name.LocalName)
                    If strName = "shapes" Then
                        blnPageShapes = True
                        xelemPageShapes = elem
                    End If
                Next elem
                If blnPageShapes = False Then
                    ' make sure we have a shapes element, since our template did not
                    xelemPageShapes = visPage.addShapesXelementToXDoc()
                End If

                Dim strValue As String = Nothing
                strValue = xelemPageShapes.@xmlns
                If strValue IsNot Nothing Then
                    Dim attNS As XAttribute = xelemPageShapes.Attribute("xmlns")
                    attNS.Remove()
                End If

                Dim xelemNewShapes As XElement = xelemPageShapes
                Dim xelemMasterShape As XElement = Nothing
                Dim xelemMasterShapes As XElement = Nothing
                Dim intMasterShapeCt As Integer = 0
                Dim xdocTemp As XDocument = New XDocument



                ' there appears to be a problem with Visio when it has to read a modified
                ' package in that where shapeid's are supposed to be unique to a page it
                ' handles them as globals until it gets the file cleaned up. This means that
                ' if you have duplicate shapeids (1, 2, 3 for instance on two pages) it can't 
                ' handle it. So for the demo code I started the second page at a higher range

                Dim intNextShapeId As Integer = maxShapeId

                ' first we add the shapes to the page

                If dtSiteData Is Nothing Then
                    getExcelSiteData()
                End If

                For Each temprow In dtSiteData.Rows

                    Dim dblPinX As Double = CDbl(temprow.item("PinX"))
                    Dim dblPinY As Double = CDbl(temprow.item("PinY"))
                    Dim strName As String = temprow.item("Name")
                    strMasterNameU = temprow.item("Shape")

                    ' get the primary shape from the xdoc and make it a separate xdoc so we
                    ' can send it over to be modified
                    xdocTemp = Nothing
                    xdocTemp = New XDocument

                    If blnUseMasters = True Then

                        ' go get the master shape file if we are using them
                        If visMasters.dictMastersNameU.TryGetValue(strMasterNameU, masterData) Then
                            visMaster.visPackage = lclVisPackage
                            visMaster.strMasterFile = masterData.mstFile

                            Dim part As PackagePart = package.GetPart(New Uri(masterData.mstFile, UriKind.Relative))
                            '  Load the document XML in the part into an XDocument instance.
                            visMaster.xDoc = XDocument.Load(XmlReader.Create(part.GetStream()))
                        Else
                            MsgBox("Unable to find master " & strMasterNameU)
                            Exit Sub
                        End If

                        ' get the root shape data
                        visMaster.loadXelemMasterShapes()
                        ' store the next shape id
                        visMaster.lngNewRootShapeId = intNextShapeId
                        visMaster.lngCurrShapeId = intNextShapeId

                        visMaster.updateXelemMasterShapeIds(visMaster.xelemMasterShapes, intNextShapeId, "root")
                        ' point past the last created shape.id in the master
                        intNextShapeId = visMaster.lngCurrShapeId + 1

                        ' load the root shape into an xdoc
                        xdocTemp.Add(visMaster.xelemMasterShapes.Elements.FirstOrDefault)

                        xelemMasterShape = Nothing
                        xelemMasterShape = buildNewShapeXelement(lclVisPackage, xdocTemp, _
                                strName, "", _
                                  dblPinX, dblPinY, visMaster.lngNewRootShapeId, _
                                  True, False, True, masterData.mstId)

                    Else
                        xdocTemp = XDocument.Parse(clsShape.strSimpleShape)
                        xelemMasterShape = buildNewShapeXelement(lclVisPackage, xdocTemp, _
                                strName, "", _
                                  dblPinX, dblPinY, _
                                  intNextShapeId, _
                                  True, blnAddNetworkProperties)
                        intNextShapeId = intNextShapeId + 1
                    End If

                    ' add the shape to the page after cleaning out extraneous namespaces
                    ' remove extraneous namespace, since it should be inherited from the page
                    Dim strNS As String = "xmlns=""http://schemas.microsoft.com/office/visio/2012/main"""
                    Dim strNewShape As String = xelemMasterShape.ToString
                    Dim intStart As Integer = strNewShape.Length
                    strNewShape = strNewShape.Replace(strNS, "")
                    Dim intEnd As Integer = strNewShape.Length
                    ' Debug.Print(strName & " " & CStr(intStart) & " " & CStr(intEnd))
                    xelemMasterShape = (XElement.Load((XmlReader.Create(New StringReader(strNewShape)))))

                    xelemNewShapes.Add(xelemMasterShape)

                Next temprow

                ' add the datalinks (and associated shapes) table if it exists
                addLinkDataTableToPage(lclVisPackage, visPage, xelemNewShapes, intNextShapeId)

                ' replace the pageshapes xelement with the one that has additional shapes
                xelemPageShapes.ReplaceWith(xelemNewShapes)

                Const bufSize As Integer = &H1000
                Dim uniEncoding As New UTF8Encoding
                Dim byteXML As Byte()

                ' clean out any empty namespace attributes
                Dim strEmptyNS As String = " xmlns="""""
                Dim strReplace As String = visPage.xDoc.ToString
                strReplace = strReplace.Replace(strEmptyNS, " ")

                Dim intInst As Integer = strReplace.Contains(strUTF)
                If intInst = 0 Then
                    strReplace = strUTF & strReplace
                End If


                byteXML = uniEncoding.GetBytes(strReplace)
                ' overwrite the windows package part with the updated data
                Using memStream As New MemoryStream(bufSize)
                    memStream.Write(byteXML, 0, byteXML.Length)
                    lclVisPackage.CopyStream(memStream, packagePartPage.GetStream())
                End Using ' close the memory stream


                xelemPageShapes = Nothing
                xelemNewShapes = Nothing

                package.Close()
                GC.Collect()

                maxShapeId = intNextShapeId

            End Using


        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        visMaster = Nothing
        visMasters = Nothing
        visShape = Nothing
        visPage = Nothing
        visPages = Nothing
        lclVisPackage = Nothing
        GC.Collect()

    End Sub


    '
    ' this method adds the dynamic connectors
    '
    Private Sub addLinkDataTableToPage _
        (ByRef lclVisPackage As clsVisPackage, _
         ByRef visPage As clsPage, _
         ByRef xelemNewShapes As XElement, _
         ByRef intNextShapeId As Integer)

        Try

            If dtLinkData Is Nothing Then Exit Sub

            Dim xdocTemp As XDocument = New XDocument
            Dim xelemDCShape As XElement = Nothing

            '
            ' now add the dynamic connectors to the page
            '
            For Each temprow In dtLinkData.Rows

                Dim strSiteA As String = temprow.item("SiteA")
                Dim strConnA As String = temprow.item("ConnA")
                Dim strSiteB As String = temprow.item("SiteB")
                Dim strConnB As String = temprow.item("ConnB")

                xdocTemp = XDocument.Parse(clsShape.strSimpleConnector)


                xelemDCShape = Nothing
                xelemDCShape = buildNewConnectorXelement(lclVisPackage, xdocTemp, _
                                                       strSiteA, strConnA, _
                                                       strSiteB, strConnB, _
                                                       intNextShapeId)

                addDynamicConnectorConnectToPage _
                    (visPage, intNextShapeId, _
                    strSiteA, "Connections." & strConnA & ".X", strConnA, _
                    strSiteB, "Connections." & strConnB & ".X", strConnB, _
                    False)

                intNextShapeId = intNextShapeId + 1
                ' add connector to the shapes object
                xelemNewShapes.Add(xelemDCShape)

            Next temprow

        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' this is part of a test to see if the Connects section of the page has impact to reading an package file
    '
    Private Sub addDynamicConnectorConnectToPage _
        (ByRef visPage As clsPage, _
         ByVal uintConnId As UInt16, _
         ByVal strShapeBegin As String, _
         ByVal strCellBegin As String, _
         ByVal strPartBegin As String, _
         ByVal strShapeEnd As String, _
         ByVal strCellEnd As String, _
         ByVal strPartEnd As String, _
         Optional ByVal blnSkip As Boolean = False)

        If blnSkip = True Then Exit Sub

        Dim visShape As clsShape = New clsShape


        Try

            Dim strPageName As String = visPage.strPgName

            Dim xelemConnects As XElement = visPage.existsSectionConnects
            If xelemConnects Is Nothing Then
                visPage.addConnectsXelementToXDoc()
            End If

            Dim blnShapeBegin As Boolean = False
            Dim intShapeIdA As Integer = -1
            Dim intShapeIdB As Integer = -1
            ' go find a shape with a NameU 
            Dim qryShapeA = From shpSelA In visPage.xDoc.Descendants _
                Select shpSelA

            Dim xelemShape As XElement = Nothing
            For Each xelemShape In qryShapeA
                If LCase(xelemShape.Name.LocalName.ToString) = "shape" Then
                    Dim listOfAttributesA As IEnumerable(Of XAttribute) = _
                        From att In xelemShape.Attributes() _
                        Select att
                    For Each att As XAttribute In listOfAttributesA
                        If LCase(att.Name.LocalName) = "nameu" Then
                            If LCase(xelemShape.@NameU) = LCase(strShapeBegin) Then
                                intShapeIdA = CShort(xelemShape.@ID)
                                GoTo ShapeIDA
                            End If
                        End If
                    Next att
                End If
            Next xelemShape

ShapeIDA:
            If intShapeIdA = -1 Then
                MsgBox("Unable to find shape " & strShapeBegin & " on page " & strPageName)
                Exit Sub
            End If

            Dim intShapePartA As Integer = -1
            Using stringReader As New StringReader(xelemShape.ToString)
                visShape.xDoc = XDocument.Load(XmlReader.Create(stringReader))
                intShapePartA = visShape.getConnPtRowIndex(strPartBegin)
            End Using

            For Each xelemShape In qryShapeA
                If LCase(xelemShape.Name.LocalName.ToString) = "shape" Then
                    Dim listOfAttributesA As IEnumerable(Of XAttribute) = _
                        From att In xelemShape.Attributes() _
                        Select att
                    For Each att As XAttribute In listOfAttributesA
                        If LCase(att.Name.LocalName) = "nameu" Then
                            If LCase(xelemShape.@NameU) = LCase(strShapeEnd) Then
                                intShapeIdB = CShort(xelemShape.@ID)
                                GoTo ShapeIDB
                            End If
                        End If
                    Next att
                End If
            Next xelemShape

ShapeIDB:
            If intShapeIdB = -1 Then
                MsgBox("Unable to find shape " & strShapeEnd & " on page " & strPageName)
                Exit Sub
            End If

            Dim intShapePartB As Integer = -1
            Using stringReader As New StringReader(xelemShape.ToString)
                visShape.xDoc = XDocument.Load(XmlReader.Create(stringReader))
                intShapePartB = visShape.getConnPtRowIndex(strPartEnd)
            End Using

            visPage.addConnectToConnects _
                    (uintConnId, _
                    "BeginX", _
                    9, _
                    intShapeIdA, _
                    strCellBegin, _
                    intShapePartA)

            visPage.addConnectToConnects _
                    (uintConnId, _
                    "EndX", _
                    12, _
                    intShapeIdB, _
                    strCellEnd, _
                    intShapePartB)

            ' Debug.Print("add connect to connects for " & CStr(uintConnId) & "- complete ")



        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        visShape = Nothing


    End Sub

    '
    ' this is the routine to add two shapes and a dynamic connector to page one
    ' shapeId's 1 thru 3
    '
    Public Sub addSimpleShapeToPage _
        (ByVal strPackagePath As String, _
         Optional ByVal strPageNameU As String = "Page-1")


        Dim lclVisPackage As clsVisPackage = New clsVisPackage
        lclVisPackage.fileCurrent = strPackagePath
        Dim visMasters As clsMasters = New clsMasters
        Dim visMaster As clsMaster = New clsMaster
        Dim visPages As clsPages = New clsPages
        Dim visPage As clsPage = New clsPage
        Dim visShape As clsShape = New clsShape

        Try

            ' our test drawing should only have a single page
            visPages.loadPagesXDoc(lclVisPackage)
            ' just build the page dictionary
            visPages.loadPages(Nothing)

            Dim pageData As stcPageData = Nothing
            If visPages.dictPagesNameU.TryGetValue(strPageNameU, pageData) Then
                visPage.loadPageXDoc(lclVisPackage, visPages.dictPagesNameU(strPageNameU).pgFile)
            Else
                MsgBox("Unable to find page " & strPageNameU)
                Exit Sub
            End If

            Using package As Package = package.Open(strPackagePath, FileMode.Open, FileAccess.ReadWrite)

                Dim partUriPage As Uri = PackUriHelper.CreatePartUri(New Uri(pageData.pgFile, UriKind.Relative))
                Dim packagePartPage As PackagePart = package.GetPart(partUriPage)
                Dim tempDoc As XDocument = XDocument.Load(XmlReader.Create(packagePartPage.GetStream()))
                ' get the page contents
                visPage = New clsPage(lclVisPackage, tempDoc)
                ' make sure we have a shapes element, since our template did not
                visPage.addShapesXelementToXDoc()
                ' start the element drilldown
                Dim xelemShapes As XElement = Nothing
                Dim qryContents = visPage.xDoc...<PageContents>
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In qryContents.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    Dim strName As String = LCase(elem.Name.LocalName)
                    If strName = "shapes" Then
                        xelemShapes = elem
                    End If
                Next elem

                Dim xelemNewShapes As XElement = xelemShapes
                Dim xelemShape As XElement = Nothing
                Dim visCell As stcCell = New stcCell

                Dim strShapeA As String = "ShapeA"
                Dim strShapeB As String = "ShapeB"

                ' get our simple/rectangle template shape
                Dim xdocShape As XDocument = XDocument.Parse(clsShape.strSimpleShape)
                xelemShape = buildNewShapeXelement(lclVisPackage, xdocShape, _
                                           strShapeA, "", 3, 5.5, 1, _
                                           True, False)

                ' add shapea to the shapes object
                xelemNewShapes.Add(xelemShape)

                ' move the second shape to the right of the first shape
                xdocShape = XDocument.Parse(clsShape.strSimpleShape)
                xelemShape = buildNewShapeXelement(lclVisPackage, xdocShape, _
                            strShapeB, "", 5, 5.5, 2, _
                            True, False)
                ' add shapeb to the shapes object
                xelemNewShapes.Add(xelemShape)

                ' get our dynamic connector template string
                xdocShape = XDocument.Parse(clsShape.strSimpleConnector)
                xelemShape = buildNewConnectorXelement(lclVisPackage, xdocShape, _
                                                       strShapeA, "Right", _
                                                       strShapeB, "Left", _
                                                       3)

                ' as part of our building the shape xelement we inserted connection points into
                ' the shapesheet, this allows us to name the endpoints of our dynamic connector
                addDynamicConnectorConnectToPage _
                    (visPage, 3, _
                    strShapeA, "Connections." & "Right" & ".X", "Right", _
                    strShapeB, "Connections." & "Left" & ".X", "Left", _
                    False)

                ' add connector to the shapes object
                xelemNewShapes.Add(xelemShape)

                ' update the shapes portion of the page with the new shapes
                xelemShapes.ReplaceWith(xelemNewShapes)

                ' visual studio get's confused and changes the namespaces
                Dim strEmptyNS As String = " xmlns="""""
                Dim strReplace As String = visPage.xDoc.ToString
                strReplace = strReplace.Replace(strEmptyNS, " ")


                Dim intInst As Integer = strReplace.Contains(strUTF)
                If intInst = 0 Then
                    strReplace = strUTF & strReplace
                End If

                Const bufSize As Integer = &H1000
                Dim uniEncoding As New UTF8Encoding
                Dim byteXML As Byte()

                byteXML = uniEncoding.GetBytes(strReplace)
                ' overwrite the windows package part with the updated data
                Using memStream As New MemoryStream(bufSize)
                    memStream.Write(byteXML, 0, byteXML.Length)
                    lclVisPackage.CopyStream(memStream, packagePartPage.GetStream())
                End Using ' close the memory stream

                package.Close()
                GC.Collect()

            End Using



        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        visMaster = Nothing
        visMasters = Nothing
        visPage = Nothing
        visPages = Nothing
        lclVisPackage = Nothing
        GC.Collect()


    End Sub

    '
    ' pass in a shape template, pinx, piny, and shapeid - return updated shape element
    ' optionally add connectionpoints, example shapedata fields, and a master.id (relationship id)
    '
    Public Function buildNewShapeXelement _
            (ByVal lclVisPackage As clsVisPackage, _
             ByVal xdocShape As XDocument, _
             ByVal strNameU As String, _
             ByVal strTextIn As String, _
             ByVal doublePinX As Double, _
             ByVal doublePinY As Double, _
             ByVal intNewId As Int32, _
             Optional ByVal blnAddConnPts As Boolean = True, _
             Optional ByVal blnAddShapeDataNetworkFields As Boolean = False, _
             Optional ByVal blnUseShapeNameAsText As Boolean = True, _
             Optional ByVal intMasterId As Integer = -1) _
         As XElement

        Dim xelemReturn As XElement = Nothing
        Dim visShape As clsShape = Nothing
        Dim visCell As stcCell = New stcCell

        Try
            ' add a set of standard connection points to the shape
            If visShape IsNot Nothing Then
                visShape = Nothing
            End If
            visShape = New clsShape
            visShape.visPackage = lclVisPackage
            visShape.xDoc = xdocShape


            ' optionally add the standard connection points
            If blnAddConnPts = True Then
                visShape.initDictConnPtsFields()
                visShape.AddStandardConnections()
            End If

            ' optionally add shapedata to the new shape
            If blnAddShapeDataNetworkFields = True Then
                visShape.addShapeDataNetworkAttributes()
            End If

            ' update the fielddata value cell with the correct shape name
            Dim xelemRowFieldData As XElement = Nothing
            If visShape.existsSection("Field") IsNot Nothing Then
                xelemRowFieldData = visShape.getRowFieldData(visShape.existsSection("Field"), 0)
                If xelemRowFieldData IsNot Nothing _
                    And blnUseShapeNameAsText = True Then
                    Dim strRowData As String = xelemRowFieldData.ToString
                    strRowData = strRowData.Replace("ShapeA", strTextIn)
                    xelemRowFieldData.ReplaceWith((XElement.Load((XmlReader.Create(New StringReader(strRowData))))))
                End If
            End If

            ' replace the default in the text section shapename with the new shapename 

            Dim xelemText As XElement = Nothing
            Dim strText As String = ""
            xelemText = visShape.getSectionText
            If xelemText IsNot Nothing Then
                If blnUseShapeNameAsText = True Then
                    strText = xelemText.ToString
                    strText = strText.Replace("ShapeA", strTextIn)
                Else
                    strText = strTextIn
                End If

                Dim myXmlReaderSettings As XmlReaderSettings = New XmlReaderSettings
                myXmlReaderSettings.IgnoreComments = True
                myXmlReaderSettings.IgnoreWhitespace = False
                Dim strReader As New StringReader(strText)
                Dim myXmlReader As XmlReader = XmlReader.Create(strReader)
                Dim objValue As Object = myXmlReader.Value
                Dim xelemNewText = XElement.Load(myXmlReader, LoadOptions.PreserveWhitespace)
                xelemText.ReplaceWith(xelemNewText)
            End If




            visCell.Reset()
            visCell.cellName = "PinX"
            visCell.cellValue = CStr(doublePinX)
            visShape.setShapeCell("PinX", visCell, "<?NewValue V?>")
            visCell.Reset()
            visCell.cellName = "PinY"
            visCell.cellValue = CStr(doublePinY)
            visShape.setShapeCell("PinY", visCell, "<?NewValue V?>")


            ' our sample simple shape has fielddata pointing to the shape name
            ' and the shape text also is initialized with a default value so
            ' both of these have to be changed


            xelemReturn = visShape.xDoc.Elements.FirstOrDefault
            Dim strValue As String = Nothing
            strValue = xelemReturn.@xmlns
            If strValue IsNot Nothing Then
                Dim attNS As XAttribute = xelemReturn.Attribute("xmlns")
                attNS.Remove()
            End If
            xelemReturn.SetAttributeValue("Name", strNameU)
            xelemReturn.SetAttributeValue("IsCustomName", "1")
            xelemReturn.SetAttributeValue("NameU", strNameU)
            xelemReturn.SetAttributeValue("IsCustomNameU", "1")
            xelemReturn.SetAttributeValue("ID", CStr(intNewId))
            ' if a masterid has been passed make sure that it is referenced
            If -1 < intMasterId Then
                xelemReturn.SetAttributeValue("Master", CStr(intMasterId))
            End If
            'If strValue IsNot Nothing Then
            '    xelemReturn.SetAttributeValue("xmlns", strValue)
            'End If

        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        visShape = Nothing
        visCell = Nothing


        Return xelemReturn

    End Function

    '
    ' pass in the desired shapes and connection points, return a dynamic connector
    ' we add a name to the connector based on the end-points
    '
    Public Function buildNewConnectorXelement _
        (ByVal lclVisPackage As clsVisPackage, _
         ByVal xdocShape As XDocument, _
         ByVal strShapeANameU As String, _
         ByVal strShapeAConnPt As String, _
         ByVal strShapeBNameU As String, _
         ByVal strShapeBConnPt As String, _
         ByVal intNewId As Int32) _
     As XElement

        Dim xelemReturn As XElement = Nothing
        Dim visShape As clsShape = Nothing
        Dim visCell As stcCell = New stcCell

        Try
            Dim strLinkName As String = strShapeANameU & "_to_" & strShapeBNameU
            If visShape IsNot Nothing Then
                visShape = Nothing
            End If
            visShape = New clsShape
            visShape.xDoc = xdocShape

            ' set the shape attributes
            xelemReturn = visShape.xDoc.Elements.FirstOrDefault
            xelemReturn.SetAttributeValue("Name", strLinkName)
            xelemReturn.SetAttributeValue("IsCustomName", "1")
            xelemReturn.SetAttributeValue("NameU", strLinkName)
            xelemReturn.SetAttributeValue("IsCustomNameU", "1")
            xelemReturn.SetAttributeValue("ID", CStr(intNewId))

            ' set the 1D transform for our end shapes
            visShape.update1DEndPointCells(strShapeANameU, strShapeAConnPt, strShapeBNameU, strShapeBConnPt)

            ' set the trigger events for the end shapes
            visCell.Reset()
            visCell.cellName = "BegTrigger"
            visCell.cellValue = "1"
            Dim strXFTrigger As String = strShapeANameU & "!EventXFMod"
            visCell.cellFormula = "_XFTRIGGER(" & strXFTrigger & ")"
            visShape.setShapeCell("BegTrigger", visCell)
            visCell.Reset()
            visCell.cellName = "EndTrigger"
            visCell.cellValue = "1"
            strXFTrigger = strShapeBNameU & "!EventXFMod"
            visCell.cellFormula = "_XFTRIGGER(" & strXFTrigger & ")"
            visShape.setShapeCell("EndTrigger", visCell)

            ' set the line routing for a straight connector
            visCell.Reset()
            visCell.cellName = "ShapeRouteStyle"
            visCell.cellValue = "2"
            visShape.setShapeCell("ShapeRouteStyle", visCell, "<?NewValue V?>")
            visCell.Reset()
            visCell.cellName = "ConLineRouteExt"
            visCell.cellValue = "1"
            visShape.setShapeCell("ConLineRouteExt", visCell, "<?NewValue V?>")


            xelemReturn = visShape.xDoc.Elements.FirstOrDefault

            GC.Collect()

        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        visShape = Nothing
        visCell = Nothing
        GC.Collect()

        Return xelemReturn

    End Function

    '
    ' pass in the page and apply the shapedata to the shapes on the page
    ' based on the dtNetData table that was imported from the excel workbook
    '
    Public Sub updatePageShapeData _
                (ByVal strPackagePath As String, _
                ByVal strPageNameU As String)

        Dim lclVisPackage As clsVisPackage = New clsVisPackage
        lclVisPackage.fileCurrent = strPackagePath

        Dim visPages As clsPages = New clsPages
        Dim visPage As clsPage = New clsPage
        Dim intPgId As Integer = -1
        Dim visShape As clsShape = New clsShape
        Dim visCell As stcCell = New stcCell(Nothing, Nothing, Nothing, Nothing)

        Dim xelemPageShapes As XElement = Nothing
        Dim xelemNewPageShapes As XElement = Nothing
        Dim xelemShape As XElement = Nothing
        Dim xelemNewShape As XElement = Nothing
        Dim xelemSectionProperty As XElement = Nothing
        Dim xelemNewSectionProperty As XElement = Nothing
        Dim xelemRow As XElement = Nothing
        Dim xelemNewRow As XElement = Nothing
        Dim xelemCell As XElement = Nothing

        Dim strShapeName As String = ""
        Dim strColName As String = ""

        Try

            If dtNetData Is Nothing Then
                getExcelSiteData()
            End If

            visPages.loadPagesXDoc(lclVisPackage)
            ' just build the page dictionary
            visPages.loadPages(Nothing)

            Dim pageData As stcPageData = Nothing
            If visPages.dictPagesNameU.TryGetValue(strPageNameU, pageData) Then
                visPage.loadPageXDoc(lclVisPackage, visPages.dictPagesNameU(strPageNameU).pgFile)
                intPgId = visPages.dictPagesNameU(strPageNameU).pgId
            Else
                MsgBox("Unable to find page " & strPageNameU)
                GoTo ExitHandler
            End If

            lclVisPackage.buildPageAndShapeDictionaries()

            Using package As Package = package.Open(strPackagePath, FileMode.Open, FileAccess.ReadWrite)

                Dim partUriPage As Uri = PackUriHelper.CreatePartUri(New Uri(pageData.pgFile, UriKind.Relative))
                Dim packagePartPage As PackagePart = package.GetPart(partUriPage)
                Dim tempDoc As XDocument = XDocument.Load(XmlReader.Create(packagePartPage.GetStream()))
                ' get the page contents
                visPage = New clsPage(lclVisPackage, tempDoc)
                ' start the element drilldown on the page

                Dim blnPageShapes As Boolean = False
                Dim qryContents = visPage.xDoc...<PageContents>
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In qryContents.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    Dim strName As String = LCase(elem.Name.LocalName)
                    If strName = "shapes" Then
                        blnPageShapes = True
                        xelemPageShapes = elem
                        xelemNewPageShapes = xelemPageShapes
                    End If
                Next elem
                If blnPageShapes = False Then
                    GoTo ClosePackage
                End If


                Dim drRow As System.Data.DataRow
                Dim intUpdatedShapeCt As Integer = 0
                Dim intRequestedShapeCt As Integer = 0

                For Each drRow In dtNetData.Rows
                    intRequestedShapeCt += 1
                    strShapeName = drRow.Item("visNameU").ToString

                    Dim qryShape = From shapeSel In xelemNewPageShapes.<Shape>
                            Where shapeSel.@NameU = strShapeName
                    Dim intShapeCt As Integer = qryShape.Count
                    If intShapeCt = 1 Then
                        ' init the visShape class
                        visShape = Nothing
                        visShape = New clsShape
                        visShape.visPackage = lclVisPackage
                        ' create an xdoc for the visShape class
                        Dim xdocShape As XDocument = Nothing
                        xdocShape = New XDocument
                        xdocShape.Add(qryShape)
                        visShape.xDoc = xdocShape

                        ' get the xelement shape information
                        xelemShape = qryShape.FirstOrDefault
                        xelemNewShape = xelemShape


                        Dim qrySectionProperty = From propSel In xelemShape...<Section>
                                Where propSel.@N = "Property"

                        If qrySectionProperty IsNot Nothing Then
                            xelemSectionProperty = qrySectionProperty.FirstOrDefault
                            xelemNewSectionProperty = xelemSectionProperty
                            Dim colData As System.Data.DataColumn

                            For Each colData In dtNetData.Columns
                                strColName = colData.ColumnName
                                If visShape.existsNamedRow(xelemNewSectionProperty, strColName) = True Then
                                    xelemRow = visShape.getNamedRow(xelemNewSectionProperty, strColName)
                                    If xelemRow IsNot Nothing Then
                                        xelemNewRow = xelemRow
                                        Dim qryCell = From cellSel In xelemNewRow.<Cell>
                                                Where cellSel.@N = "Value"
                                        xelemCell = qryCell.FirstOrDefault
                                        visCell.readXElement(qryCell.FirstOrDefault)
                                        visCell.cellValue = drRow(colData).ToString
                                        xelemCell.ReplaceWith(visCell.writeXelement)
                                        xelemRow.ReplaceWith(xelemNewRow)
                                    End If ' test for xelemrow returned
                                End If ' test for named row exists in section
                            Next colData
                            xelemSectionProperty.ReplaceWith(xelemNewSectionProperty)
                        End If ' test for section_property exists
                        xelemShape.ReplaceWith(xelemNewShape)
                        intUpdatedShapeCt += 1
                    End If ' test for shapect
                Next drRow

                xelemPageShapes.ReplaceWith(xelemNewPageShapes)

                Const bufSize As Integer = &H1000
                Dim uniEncoding As New UTF8Encoding
                Dim byteXML As Byte()

                Dim strEmptyNS As String = " xmlns="""""
                Dim strReplace As String = tempDoc.ToString
                strReplace = strReplace.Replace(strEmptyNS, " ")

                Dim intInst As Integer = strReplace.Contains(strUTF)
                If intInst = 0 Then
                    strReplace = strUTF & strReplace
                End If

                byteXML = uniEncoding.GetBytes(strReplace)
                ' overwrite the windows package part with the updated data
                Using memStream As New MemoryStream(bufSize)
                    memStream.Write(byteXML, 0, byteXML.Length)
                    lclVisPackage.CopyStream(memStream, packagePartPage.GetStream())
                End Using ' close the memory stream

                'MsgBox("Requested Updates = " & intRequestedShapeCt & ", Updated = " & intUpdatedShapeCt)

ClosePackage:
                package.Close()
                GC.Collect()

            End Using


            GC.Collect()

        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

ExitHandler:

        visShape = Nothing
        visPage = Nothing
        visPages = Nothing
        lclVisPackage = Nothing
        GC.Collect()

    End Sub

    '
    ' this is the routine to add a titleblock to page
    '
    Public Sub addTextRunToPage _
        (ByVal strPackagePath As String, _
         Optional ByVal strPageNameU As String = "Page-1", _
         Optional ByVal arrString As ArrayList = Nothing)


        Dim lclVisPackage As clsVisPackage = New clsVisPackage
        lclVisPackage.fileCurrent = strPackagePath
        Dim visMasters As clsMasters = New clsMasters
        Dim visMaster As clsMaster = New clsMaster
        Dim visPages As clsPages = New clsPages
        Dim visPage As clsPage = New clsPage
        Dim visShape As clsShape = New clsShape

        Dim xelemSectionChar As XElement = Nothing
        Dim xelemRowChar As XElement = Nothing
        Dim xelemSectionPara As XElement = Nothing
        Dim xelemRowPara As XElement = Nothing
        Dim xelemSectionText As XElement = Nothing
        Dim xelemSectionTab As XElement = Nothing
        Dim xelemNewSectionChar As XElement = Nothing
        Dim xelemNewSectionPara As XElement = Nothing
        Dim xelemNewSectionText As XElement = Nothing
        Dim xelemNewSectionTab As XElement = Nothing


        Dim cellFont As stcCell = New stcCell
        Dim cellColor As stcCell = New stcCell
        Dim cellStyle As stcCell = New stcCell
        Dim cellSize As stcCell = New stcCell
        Dim cellComplexScriptFont As stcCell = New stcCell
        Dim cellPos As stcCell = New stcCell


        Dim cellHorzAlign As stcCell = New stcCell
        Dim cellIndLeft As stcCell = New stcCell
        Dim cellIndRight As stcCell = New stcCell
        Dim cellSpBefore As stcCell = New stcCell
        Dim cellSpAfter As stcCell = New stcCell


        Dim strShapeTitle As String = "TextRun"

        Const xmlTab As String = "&#x9;"
        Const xmlLF As String = "&#xA;"
        Const xmlCR As String = "&#xD;"
        Const xmlSp As String = "&#x20;"
        Const xmlCrLf As String = "&#xD;&#xA;"

        Try

            If arrString Is Nothing Then
                arrString = New ArrayList
                arrString.Add("Courier New Font, 14Pt., paragraph is center aligned." & vbLf)
                arrString.Add("Arial Font, 10Pt., paragraph is left aligned, with a 0.3 in. indent on the left and right" & vbLf)
                arrString.Add("Using our default paragraph parameters, text is left aligned with no indenting. The paragraphs have an SpBefore equal to the 10pt font size" & vbLf)
            End If



            ' our test drawing should only have a single page
            visPages.loadPagesXDoc(lclVisPackage)
            ' just build the page dictionary
            visPages.loadPages(Nothing)

            Dim pageData As stcPageData = Nothing
            If visPages.dictPagesNameU.TryGetValue(strPageNameU, pageData) Then
                visPage.loadPageXDoc(lclVisPackage, visPages.dictPagesNameU(strPageNameU).pgFile)
            Else
                MsgBox("Unable to find page " & strPageNameU)
                Exit Sub
            End If

            Using package As Package = package.Open(strPackagePath, FileMode.Open, FileAccess.ReadWrite)

                Dim partUriPage As Uri = PackUriHelper.CreatePartUri(New Uri(pageData.pgFile, UriKind.Relative))
                Dim packagePartPage As PackagePart = package.GetPart(partUriPage)
                Dim myXmlReaderSettings As XmlReaderSettings = New XmlReaderSettings
                myXmlReaderSettings.IgnoreComments = True
                myXmlReaderSettings.IgnoreWhitespace = False
                myXmlReaderSettings.ConformanceLevel = ConformanceLevel.Document
                myXmlReaderSettings.IgnoreProcessingInstructions = False
                Dim myXmlReader As XmlReader = XmlReader.Create(packagePartPage.GetStream(), myXmlReaderSettings)

                Dim tempDoc As XDocument = XDocument.Load(myXmlReader)

                ' get the page contents
                visPage = New clsPage(lclVisPackage, tempDoc)
                ' make sure we have a shapes element, since our template did not
                visPage.addShapesXelementToXDoc()
                ' start the element drilldown
                Dim xelemShapes As XElement = Nothing
                Dim qryContents = visPage.xDoc...<PageContents>
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In qryContents.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    Dim strName As String = LCase(elem.Name.LocalName)
                    If strName = "shapes" Then
                        xelemShapes = elem
                        ' only want the first one
                        Exit For
                    End If
                Next elem

                Dim xelemNewShapes As XElement = xelemShapes
                Dim xelemShape As XElement = Nothing
                Dim visCell As stcCell = New stcCell


                ' get our simple/rectangle template shape
                Dim xdocShape As XDocument = XDocument.Parse(clsShape.strSimpleShape)

                visShape.xDoc = xdocShape
                visCell.Reset()
                visCell.cellName = "Height"
                visCell.cellValue = "2.5"
                visShape.setShapeCell("Height", visCell)
                visCell.Reset()
                visCell.cellName = "Width"
                visCell.cellValue = "3"
                visShape.setShapeCell("Width", visCell)

                '**********************************************************
                ' add character section if not there
                ' add the details when we build the text section
                xelemSectionChar = visShape.existsSection("Character")
                If xelemSectionChar IsNot Nothing Then
                    xelemNewSectionChar = xelemSectionChar
                Else
                    xelemSectionChar = visShape.createSectionCharacter
                    visShape.xDoc.Root.Add(xelemSectionChar)
                    xelemNewSectionChar = xelemSectionChar
                End If

                '**********************************************************
                ' add paragraph section if not there
                xelemSectionPara = visShape.existsSection("Paragraph")
                If xelemSectionPara IsNot Nothing Then
                    xelemNewSectionPara = xelemSectionPara
                Else
                    xelemSectionPara = visShape.createSectionParagraph
                    visShape.xDoc.Root.Add(xelemSectionPara)
                    xelemNewSectionPara = xelemSectionPara
                End If

                ' add three paragraph rows for the formats that we want to use
                ' the first paragraph will be center aligned
                cellHorzAlign.Reset()
                cellHorzAlign.cellName = "HorzAlign"
                cellHorzAlign.cellValue = CStr(visShape.msoParagraphAlign.msoAlignCenter)

                ' add paragraph index 0
                xelemRowPara = visShape.createRowParagraph(0, False, _
                                stcHorzAlign:=cellHorzAlign)

                xelemNewSectionPara.Add(xelemRowPara)


                ' the second paragraph will be left aligned and indented left and right
                cellHorzAlign.Reset()
                cellHorzAlign.cellName = "HorzAlign"
                cellHorzAlign.cellValue = CStr(visShape.msoParagraphAlign.msoAlignLeft)
                cellIndLeft.Reset()
                cellIndLeft.cellName = "IndLeft"
                cellIndLeft.cellValue = "0.3"
                cellIndRight.Reset()
                cellIndRight.cellName = "IndRight"
                cellIndRight.cellValue = "0.3"
                cellSpBefore.Reset()
                cellSpBefore.cellName = "SpBefore"
                ' even though we specify units, the value is in inches......
                ' in this case we are using the value of the 10pt text for a single line 
                cellSpBefore.cellValue = "0.1388888888888889"
                cellSpBefore.cellUnits = "PT"
                'cellSpAfter.Reset()
                'cellSpAfter.cellName = "SpAfter"
                '' even though we specify units, the value is in inches......
                '' in this case we are using the value of the 10pt text for a single line 
                'cellSpAfter.cellValue = "0.1388888888888889"
                'cellSpAfter.cellUnits = "PT"


                xelemRowPara = visShape.createRowParagraph(1, False, _
                                stcIndLeft:=cellIndLeft, _
                                stcIndRight:=cellIndRight, _
                                stcHorzAlign:=cellHorzAlign, _
                                stcSpBefore:=cellSpBefore)

                xelemNewSectionPara.Add(xelemRowPara)

                ' the final paragraph (default) will be left aligned with no indenting
                cellHorzAlign.Reset()
                cellHorzAlign.cellName = "HorzAlign"
                cellHorzAlign.cellValue = CStr(visShape.msoParagraphAlign.msoAlignLeft)
                cellSpBefore.Reset()
                cellSpBefore.cellName = "SpBefore"
                ' even though we specify units, the value is in inches......
                ' in this case we are using the value of the 10pt text for a single line 
                cellSpBefore.cellValue = "0.1388888888888889"
                cellSpBefore.cellUnits = "PT"

                ' add paragraph index 0
                xelemRowPara = visShape.createRowParagraph(2, False, _
                                stcHorzAlign:=cellHorzAlign, _
                                stcSpBefore:=cellSpBefore)

                xelemNewSectionPara.Add(xelemRowPara)


                xelemSectionPara.ReplaceWith(xelemNewSectionPara)

                '**********************************************************
                ' add tabs section if not there
                xelemSectionTab = visShape.existsSection("Tabs")
                If xelemSectionTab IsNot Nothing Then
                    xelemNewSectionTab = xelemSectionTab
                Else
                    xelemSectionTab = visShape.createSectionTabs
                    visShape.xDoc.Root.Add(xelemSectionTab)
                    xelemNewSectionTab = xelemSectionTab
                End If


                '**********************************************************
                ' clear the text section of contents
                xelemSectionText = visShape.getSectionText
                xelemNewSectionText = visShape.createSectionText


                Dim sbText As StringBuilder = New StringBuilder
                sbText.Append("<Text>")

                Dim intX As Integer = -1
                Dim intPos As Integer = 0
                Dim intRun As Integer = 0
                For intX = 0 To arrString.Count - 1
                    xelemRowChar = Nothing

                    Select Case intX
                        Case 0
                            cellFont.Reset()
                            cellFont.cellName = "Font"
                            cellFont.cellValue = "Courier New"
                            ' set color to red
                            cellColor.Reset()
                            cellColor.cellName = "Color"
                            cellColor.cellValue = "#ff0000"
                            cellColor.cellFormula = "THEMEGUARD(RGB(255,0,0))"
                            cellStyle.Reset()
                            cellStyle.cellName = "Style"
                            cellStyle.cellValue = "1"
                            cellSize.Reset()
                            cellSize.cellName = "Size"
                            ' (1/72) * 14 = 0.1944444444444444
                            cellSize.cellValue = "0.1944444444444444"
                            cellSize.cellUnits = "PT"
                            cellComplexScriptFont.Reset()
                            cellComplexScriptFont.cellName = "ComplexScriptFont"
                            cellComplexScriptFont.cellValue = "Courier New"

                            xelemRowChar = visShape.createRowCharacter(intRun, False, _
                                                    stcFont:=cellFont, _
                                                    stcColor:=cellColor, _
                                                    stcStyle:=cellStyle, _
                                                    stcSize:=cellSize, _
                                                    stcComplexScriptFont:=cellComplexScriptFont)

                            xelemNewSectionChar.Add(xelemRowChar)
                            Dim strNewCharSect As String = "<cp IX=""" & CStr(intRun) & """ />"
                            sbText.Append(strNewCharSect)
                            intRun = intRun + 1

                            Dim strNewParaSect As String = "<pp IX=""0"" />"
                            sbText.Append(strNewParaSect)


                        Case 1

                            ' our default is an Arial 10pt string that is indented and horizontal
                            ' aligned to the left
                            cellFont.Reset()
                            cellFont.cellName = "Font"
                            cellFont.cellValue = "Arial"
                            ' set color to blue
                            cellColor.Reset()
                            cellColor.cellName = "Color"
                            cellColor.cellValue = "#0000ff"
                            cellColor.cellFormula = "THEMEGUARD(RGB(0,0,255))"
                            cellSize.Reset()
                            cellSize.cellName = "Size"
                            ' (1/72) * 10 = 0.1388888888888889
                            cellSize.cellValue = "0.1388888888888889"
                            cellSize.cellUnits = "PT"
                            cellPos.Reset()
                            cellPos.cellName = "Pos"
                            cellPos.cellValue = CStr(intPos)

                            xelemRowChar = visShape.createRowCharacter(intRun, False, _
                                                    stcPos:=cellPos, _
                                                    stcColor:=cellColor, _
                                                    stcSize:=cellSize, _
                                                    stcFont:=cellFont)

                            xelemNewSectionChar.Add(xelemRowChar)
                            Dim strNewCharSect As String = "<cp IX=""" & CStr(intRun) & """ />"
                            sbText.Append(strNewCharSect)
                            intRun = intRun + 1

                            Dim strNewParaSect As String = "<pp IX=""1"" />"
                            sbText.Append(strNewParaSect)


                        Case Else

                            ' our default is an Arial 10pt string that is indented and horizontal
                            ' aligned to the left
                            cellFont.Reset()
                            cellFont.cellName = "Font"
                            cellFont.cellValue = "Arial"
                            cellSize.Reset()
                            cellSize.cellName = "Size"
                            ' (1/72) * 10 = 0.1388888888888889
                            cellSize.cellValue = "0.1388888888888889"
                            cellSize.cellUnits = "PT"
                            cellPos.Reset()
                            cellPos.cellName = "Pos"
                            cellPos.cellValue = CStr(intPos)

                            xelemRowChar = visShape.createRowCharacter(intRun, False, _
                                                    stcPos:=cellPos, _
                                                    stcSize:=cellSize, _
                                                    stcFont:=cellFont)

                            xelemNewSectionChar.Add(xelemRowChar)
                            Dim strNewCharSect As String = "<cp IX=""" & CStr(intRun) & """ />"
                            sbText.Append(strNewCharSect)
                            intRun = intRun + 1

                            Dim strNewParaSect As String = "<pp IX=""2"" />"
                            sbText.Append(strNewParaSect)


                    End Select


                    sbText.Append(arrString(intX))
                    intPos = intPos + Len(arrString(intX))

                Next intX


                ' close the text tab
                sbText.Append("</Text>")

                Dim strSectChar As String = xelemNewSectionChar.ToString
                Dim strSectCharEmpty As String = "<Section N=""Character"" />"

                xelemSectionChar = visShape.existsSection("Character")
                xelemSectionChar.ReplaceWith(xelemNewSectionChar)



                ' put the final touchs onto the shape
                xelemShape = buildNewShapeXelement(lclVisPackage, visShape.xDoc, _
                                           strShapeTitle, sbText.ToString, 11.5, 2.0, 999, _
                                           False, False, False)

                ' add shape to the shapes object
                xelemNewShapes.Add(xelemShape)

                ' update the shapes portion of the page with the new shapes
                xelemShapes.ReplaceWith(xelemNewShapes)

                ' visual studio get's confused and changes the namespaces
                Dim strEmptyNS As String = " xmlns="""""
                Dim strReplace As String = visPage.xDoc.ToString
                strReplace = strReplace.Replace(strEmptyNS, " ")


                Dim intInst As Integer = strReplace.Contains(strUTF)
                If intInst = 0 Then
                    strReplace = strUTF & strReplace
                End If


                Const bufSize As Integer = &H1000
                Dim uniEncoding As New UTF8Encoding
                Dim byteXML As Byte()

                byteXML = uniEncoding.GetBytes(strReplace)
                ' overwrite the windows package part with the updated data
                Using memStream As New MemoryStream(bufSize)
                    memStream.Write(byteXML, 0, byteXML.Length)
                    lclVisPackage.CopyStream(memStream, packagePartPage.GetStream())
                End Using ' close the memory stream

                package.Close()
                GC.Collect()

            End Using

        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        visMaster = Nothing
        visMasters = Nothing
        visPage = Nothing
        visPages = Nothing
        lclVisPackage = Nothing
        GC.Collect()


    End Sub

    '
    ' we add three masters to an assumed bland drawing and the stencil they are loaded from
    '
    Public Sub loadTestMasterShapes()

        ' create a local package
        Dim lclPackage As clsVisPackage = New clsVisPackage
        Dim visWindows As clsWindows = Nothing
        Dim visMasters As clsMasters = New clsMasters
        Dim strPackagePath As String = ""

        Try

            strPackagePath = My.Settings.strTestPackage

            ' add three masters to the package
            visMasters.addTestMasterObjectsToPackage _
                (strPackagePath, _
                clsWindows.strTestStencil, _
                {"Certificate server", "Router", "Dynamic connector"})

            ' now add a stencil for the user to work with
            lclPackage.fileCurrent = strPackagePath
            lclPackage.loadPkgAndParts(Nothing)

            ' get the drawing package
            Using CurrentPackage As Package = Package.Open(strPackagePath, FileMode.Open, FileAccess.ReadWrite)
                lclPackage.pkgCurrent = CurrentPackage
                visWindows = New clsWindows(lclPackage)
                visWindows.AddStencilWindow(CurrentPackage, clsWindows.strTestStencil)
                CurrentPackage.Flush()
                CurrentPackage.Close()
            End Using

            visMasters = Nothing
            visWindows = Nothing
            lclPackage = Nothing

            GC.Collect()

ExitHandler:
        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Notify Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        GC.Collect()


    End Sub


#End Region


#Region " get external excel information "



    '
    ' the three tables we will use for testing
    '
    Public Enum siteTables As Integer
        tblSiteData = 0
        tblLinkData = 1
        tblNetData = 2
    End Enum

    Private _dtSiteData As System.Data.DataTable
    Public Property dtSiteData As System.Data.DataTable
        Get
            dtSiteData = _dtSiteData
        End Get
        Set(value As System.Data.DataTable)
            _dtSiteData = value
        End Set
    End Property

    Private _dtLinkData As System.Data.DataTable
    Public Property dtLinkData As System.Data.DataTable
        Get
            dtLinkData = _dtLinkData
        End Get
        Set(value As System.Data.DataTable)
            _dtLinkData = value
        End Set
    End Property

    Private _dtNetData As System.Data.DataTable
    Public Property dtNetData As System.Data.DataTable
        Get
            dtNetData = _dtNetData
        End Get
        Set(value As System.Data.DataTable)
            _dtNetData = value
        End Set
    End Property



    '
    ' this is a simple two table read operation to support the 
    ' demo drawing, for a more extensive look at handling data
    ' see the dc_rack application connect project
    '
    Public Sub getExcelSiteData()

        Try

            ' dictTables is a list of the tables we will use in this solution
            ' enumTable value and the exported table name
            Dim dictTables As Dictionary(Of Int16, String)
            dictTables = New Dictionary(Of Int16, String)
            ' the two tables that we will work with in this solution
            dictTables.Add(siteTables.tblSiteData, "dbo_tblSiteData")
            dictTables.Add(siteTables.tblLinkData, "dbo_tblLinkData")
            dictTables.Add(siteTables.tblNetData, "dbo_tblNetData")

            Dim intTblCount As Integer = 0


            ' read the worksheet list into a local dictionary
            Dim dictTemp As Dictionary(Of Int16, String) = New Dictionary(Of Int16, String)
            dictTemp = readExcelWorksheetList(My.Settings.path_custom_workbook & My.Settings.custom_workbook)
            Dim blnContains As Boolean = True
            ' are there enough worksheets
            If 2 <= dictTemp.Count Then
                For Each kvp In dictTables
                    If dictTemp.ContainsValue(kvp.Value) = False Then
                        blnContains = False
                    End If
                Next kvp
            Else
                MsgBox("Not enough worksheets for this solutions")
                Exit Sub
            End If

            If blnContains = False Then
                MsgBox("Could not find all necessary worksheets")
                Exit Sub
            End If

            Dim dtTemp As System.Data.DataTable = Nothing
            ' now read the worksheets into the tables
            ' the keyvalue pairs are the table index, and the modified table name
            For Each kvp In dictTables
                dtTemp = readExcelWorksheet(My.Settings.path_custom_workbook & My.Settings.custom_workbook, kvp.Value)

                Select Case kvp.Key
                    Case siteTables.tblSiteData
                        dtSiteData = dtTemp
                        intTblCount = intTblCount + 1
                    Case siteTables.tblLinkData
                        dtLinkData = dtTemp
                        intTblCount = intTblCount + 1
                    Case siteTables.tblNetData
                        dtNetData = dtTemp
                        intTblCount = intTblCount + 1
                End Select
            Next kvp

            MsgBox("Data Loaded (" & intTblCount.ToString & " tables)")


        Catch xml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(xml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



#End Region


End Module
